package com.simoniu.statement.statementdemo;


import com.simoniu.entity.Students;
import com.simoniu.util.DBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/*
 * 测试Statement的用法。
 * */
public class StatementDemo {


    public List<Students> queryAllStudents() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "";
        List<Students> studentsList = new ArrayList<Students>();
        try {
            conn = DBUtil.getConnection();
            if (conn != null) {
                sql = "select * from students;";
                stmt = conn.createStatement();
                //2.执行了一个查询语句
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    Students s = new Students();
                    s.setSid(rs.getInt("sid"));
                    s.setSname(rs.getString("sname"));
                    s.setGender(rs.getString("gender"));
                    studentsList.add(s);
                }
                return studentsList;
            }
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }


    public List<Students> querStudentsByGender(String gender) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "";
        List<Students> studentsList = new ArrayList<Students>();
        try {
            conn = DBUtil.getConnection();
            if (conn != null) {
                sql = "select * from students where gender='"+gender+"';";
                stmt = conn.createStatement();
                //2.执行了一个查询语句
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    Students s = new Students();
                    s.setSid(rs.getInt("sid"));
                    s.setSname(rs.getString("sname"));
                    s.setGender(rs.getString("gender"));
                    studentsList.add(s);
                }
                return studentsList;
            }
            return null;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }


    public static void main(String[] args) throws Exception {
        List<Students> studentsList =null;
        StatementDemo demo = new StatementDemo();

        /*
        studentsList = demo.queryAllStudents();
        if(studentsList!=null){
            studentsList.forEach(System.out::println);

        }else{
            System.out.println("查询失败！");
        }*/

        studentsList = demo.querStudentsByGender("男' or '1=1");
        //studentsList = demo.querStudentsByGender("男");
        if(studentsList!=null){
            studentsList.forEach(System.out::println);
        }else{
            System.out.println("查询失败！");
        }

    }
}
